#ifndef SQLHELPER_H
#define SQLHELPER_H
#include <QString>
#include <QList>
#include <QStringList>
#include <QSqlDatabase>
#include <QSqlQuery>
#include "globaldef.h"
#include "constants.h"
#include "ettadatastruct.h"
#include "commonfunction.h"
#include <QMutex>

class SqlHelper
{
public:
    ~SqlHelper();
    static SqlHelper * getInstance();
    bool openDataBase(const QString &dbPath);
    void closeDatabase();

    bool setVersion(int major, int minor, int build);
    bool version(int &major, int &minor, int &build);

    QList<QStringList> selectAllFromTable(QString table);
    QList<User> selectAllUsers();
    QList<Role> selectAllRoles();

    QList<QStringList> selectFromTable(QSqlQuery &query);
    EttaError updateTable(QString qsQuery);
    EttaError updateTable(QSqlQuery query);
    EttaError insertTable(QSqlQuery &query);
    EttaError checkLogInfo(QString user, QString pw);
    EttaError checkLoginUserStatus(QString user);
    EttaError createTable(TableIndex index);

    //User
    EttaError insertUser(User user);
    EttaError deleteUser(User user);    //based on username
    int selectUserPermission(User user);

    //update user's role and permission
    EttaError updateUserRole(User user, Role role);
    EttaError setUserPassword(User user);

    User convertSL2User(QStringList sl);
    EttaError setUserEnable(User user);
    EttaError setFailLoginTimes(User user);
    QList<User> selectUserBasedOnName(User user);
    QList<User> selectUserBasedOnRole(QString roleName);

    // 用户表
    bool checkUserIsOn(const QString &userName);
    bool checkUserExist(const QString &userName);
    bool checkRoleHasUser(const QString roleName);

    //Role
    EttaError insertRole(Role role);
    EttaError deleteRole(Role role);
    EttaError updateRole(Role role);
    int selectRolePermission(Role role);
    EttaError updateUserPermission2Zero(Role role);

    //Select from table t_role based on name
    QList<Role> selectRoleName(Role role);
    bool checkRoleExist(const QString &roleName);

    //convert QStringList to Role
    Role convertSL2Role(QStringList sl);

    //Parameter
    EttaError insertParameterItem(Parameter p);
    //Get enable count
    QList<QStringList> parameterEnableCount();
    //Select from table t_parameter based on name
    QList<QStringList> selectParameterName(Parameter p);
    //Select from table t_parameter based on code
    QList<QStringList> selectParameterCode(Parameter p);
    QList<QStringList> selectParameterIson(Parameter p);
    EttaError updateParameterIson(Parameter p);
    EttaError updateParameterBasedOnName(Parameter p);
    //Deleter parameter based on name
    EttaError deleteParameterItem(QString name);
    Parameter convertSL2Parameter(QStringList sl);
     //Select from table t_parameter based on name
    QList<Parameter> selectParameter(Parameter p);

    // 参数表
    bool checkParamNameExist(const QString &paramName);
    bool checkParamCodeExist(const QString &paramCode);
    bool checkParamIson(const QString &paramName);

    //Sample
    EttaError insertSample(Sample s);
    bool checkSampleNameExist(const QString sampleName);

    //Material
    EttaError insertMaterial(Material m);
    EttaError insertMaterial(const QString &qrcode);
    QList<QStringList> selectMaterialSerial(Material m);
    bool checkMaterialExist(const QString &qrcode);
    int queryRowsCount(QSqlQuery &query);

    //Experiment
    EttaError insertExperimentInfo(const ExperimentInfo &expInfo);
    QList<ExperimentInfo> selectExperiment(QString startTime, QString endTime,
                                           QString username, QString parameterName,
                                           QString result, QString sampleID,
                                           QString materialBatchNo, QString materialSpec);
    ExperimentInfo convertSL2ExperimentInfo(QStringList sl);

    //Volt_test
    EttaError insertVoltTestResult(VoltTest vt);
    QList<VoltTest> selectVoltTestBasedOnTime(QString startTime, QString endTime, QString user);
    VoltTest convertSL2VoltTest(QStringList sl);

    //Speed_test
    EttaError insertFlowTestResult(FlowTest st);
    QList<FlowTest> selectFlowTestBasedOnTime(QString startTime, QString endTime, QString user);
    FlowTest convertSL2FlowTest(QStringList sl);

    //Log
    EttaError insertLogInfo(LogInfo logInfo);
    //select log based on startTime, endTime and type
    QList<LogInfo> selectLogTimeAndType(LogInfo logInfo, QString startTime, QString endTime, QString user);
    LogInfo convertSL2Loginfo(QStringList sl);

private:
    SqlHelper();

private:
    static SqlHelper * instance;
    static QSqlDatabase db;
    bool isOpen;
    QMutex mutex;

};

#endif // SQLHELPER_H
